package dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;

import dao.CollectionDao;
import domain.Collection;
import domain.Product;
import util.DataSourceUtils;

public class CollectionDaoImpl implements CollectionDao {

	@Override
	public List<Collection> findCollectionsByUser(int userId, int page, int limit) throws SQLException {
		String sql = "select collections.*,products.* from collections inner join products on collections.product_id=products.id where user_id=? limit ?,?";
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		return runner.query(sql, new ResultSetHandler<List<Collection>> () {

			@Override
			public List<Collection> handle(ResultSet rs) throws SQLException {
				List<Collection> res = new ArrayList<Collection>();
				
				while (rs.next()) {
					Collection collection = new Collection();
					Product product = new Product();
					
					product.setCategory(rs.getString("products.category"));
					product.setDescription(rs.getString("products.description"));
					product.setId(rs.getString("products.id"));
					product.setImgurl(rs.getString("products.imgurl"));
					product.setName(rs.getString("products.name"));
					product.setPnum(rs.getInt("products.pnum"));
					product.setPrice(rs.getDouble("products.price"));
					
					collection.setProduct(product);
					res.add(collection);
				}
				
				return res;
			}
			
		}, userId, (page - 1) * limit, limit);
	}

	@Override
	public boolean createCollection(String product_id, int user_id) throws SQLException {
		String sql = "insert into collections(product_id, user_id) values(?,?)";
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		try {
			runner.update(sql, product_id, user_id);
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}

	@Override
	public boolean deleteCollection(int id) throws SQLException {
		String sql = "delete from collections where id=?";
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		try {
			runner.update(sql, id);
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}

	@Override
	public Collection findCollectionById(String product_id, int userId) throws SQLException {
		String sql = "select collections.*,products.* from collections inner join products on collections.product_id=products.id where product_id=? and user_id=?";
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		return runner.query(sql, new ResultSetHandler<Collection> () {

			@Override
			public Collection handle(ResultSet rs) throws SQLException {
				Collection collection = new Collection();
				
				while (rs.next()) {
					Product product = new Product();
					
					collection.setId(rs.getInt("collections.id"));
					
					product.setCategory(rs.getString("products.category"));
					product.setDescription(rs.getString("products.description"));
					product.setId(rs.getString("products.id"));
					product.setImgurl(rs.getString("products.imgurl"));
					product.setName(rs.getString("products.name"));
					product.setPnum(rs.getInt("products.pnum"));
					product.setPrice(rs.getDouble("products.price"));
					
					collection.setProduct(product);
				}
				
				return collection;
			}
			
		}, product_id, userId);
	}

}
